package com.googlecode.flightreports.countrysummary;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement; 
import java.sql.SQLException;
import java.text.SimpleDateFormat;

import com.googlecode.kohmori.reportgen.AbstractSQLSource;
import com.googlecode.kohmori.springmvc.DefaultReportParams;
import java.util.Calendar;
import java.util.GregorianCalendar;
import org.xml.sax.InputSource;

/* 
 * This class creates the report object and uses SQL to populate its values.
 */
public class ReportSQL extends AbstractSQLSource { 

    /**
     * Creates a report object
     * @param dbConn Connection object to run SQL
     * @return report instance, null if no report found
     */
    public InputSource createReportObject(Connection dbConn, DefaultReportParams reportParams) 
    throws SQLException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MMM-dd");
        ReportObject rpt = new ReportObject();

        try {
            Calendar cal = new GregorianCalendar();
            rpt.setRunDate(cal.getTime());
            String currentCountry = "XXX";
            ReportObject.Country currCountryObj = null;
          
            String sqlStatement =
                "SELECT country_name, region, country_iso_code, " +
                " city_name, airport, language," +
                " arrival_count, departure_count " +
                " from APP.V_COUNTRY_SUMMARY " +
                " order by region, country_name, city_name ";
                    
            System.out.println(sqlStatement);
            PreparedStatement pstmt = dbConn.prepareStatement(sqlStatement);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                if (!currentCountry.equals(rs.getString("country_name"))) {
                    // create a new country object
                    currentCountry = rs.getString("country_name");
                    ReportObject.Country country = new ReportObject.Country();
                    currCountryObj = country;
                    country.setName(currentCountry);
                    country.setRegion(rs.getString("region"));
                    country.setISOCode(rs.getString("country_iso_code"));
                    ReportObject.City city = new ReportObject.City();
                    city.setName(rs.getString("city_name"));
                    city.setAirport(rs.getString("airport"));
                    city.setLanguage(rs.getString("language"));
                    city.setArrivalCount(rs.getInt("arrival_count"));
                    city.setDepartureCount(rs.getInt("departure_count"));
                    country.addCity(city);
                    rpt.addCountry(country);
                    // create a new city object
                } else {
                    // create a new city object to present country object
                    ReportObject.City city = new ReportObject.City();
                    city.setName(rs.getString("city_name"));
                    city.setAirport(rs.getString("airport"));
                    city.setLanguage(rs.getString("language"));
                    city.setArrivalCount(rs.getInt("arrival_count"));
                    city.setDepartureCount(rs.getInt("departure_count"));
                    currCountryObj.addCity(city);
                }
            }

            pstmt.close();

        } catch (SQLException e) {
            throw new SQLException("Exception in ReportSQL: " + e.getMessage());
        }
        return rpt;
    }
}
